package com.info.zhiduoduo.repository.mapper.company;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.account.CompanyAccountFlowDto;
import com.info.zhiduoduo.repository.dto.account.CrowdsourcingLedgerDto;
import com.info.zhiduoduo.repository.entity.company.CompanyAccountflow;

import com.info.zhiduoduo.repository.entity.company.CompanyProject;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

/**
 * <p>
 * 企业账户流水(收入支出明细) Mapper 接口
 * </p>
 *
 * @author bob
 * @since 2019-01-25
 */
public interface CompanyAccountflowMapper extends BaseMapper<CompanyAccountflow> {

	@Select(" select caf.*,pay.pay_name,c.compname,cr.rechargeno " +
			" from zdd_company_accountflow caf " +
			" join zdd_company c on c.compid = caf.compid " +
			" left join zdd_payment pay on caf.pay_id = pay.id " +
			" left join zdd_company_recharge cr on cr.rechargeid = caf.bizid and caf.biz_type = 4 " +
			" where if(#{flowtype}=0,1=1,caf.flowtype=#{flowtype}) " +
			" and caf.createtime between #{startDate} and #{endDate} " +
			" and concat(c.compname,IFNULL(cr.rechargeno,'')) like '%${name}%' " +
			" and if(ISNULL(#{accountType}) or #{accountType} = '',1=1, caf.account_type in (0${accountType})) " +
			" order by caf.id desc ")
	List<CompanyAccountFlowDto> getAccountFlowList(@Param("name") String name, @Param("flowtype") int flowtype, @Param("accountType") String accountType,
	                                               @Param("startDate") Date startDate, @Param("endDate") Date endDate);

	@Select(" select caf.*,pay.pay_name,pay.pay_name_alias,c.compname,cr.rechargeno " +
			" from zdd_company_accountflow caf " +
			" join zdd_company c on c.compid = caf.compid " +
			" left join zdd_payment pay on caf.pay_id = pay.id " +
			" left join zdd_company_recharge cr on cr.rechargeid = caf.bizid and caf.biz_type = 4 " +
			" where caf.compid = #{compid} " +
			" and if(#{flowtype}=0,1=1,flowtype=#{flowtype}) " +
			" and caf.createtime between #{startDate} and #{endDate} " +
			" and if(#{payId}=0, 1 = 1,caf.pay_id = #{payId}) " +
			" and concat(IFNULL(c.compname,''),IFNULL(cr.rechargeno,''),IFNULL(caf.flowname,'')) like '%${name}%' " +
			" order by caf.id desc ")
	List<CompanyAccountFlowDto> getAccountFlowListByCompid(@Param("compid") int compid, @Param("name") String name,
	                                                       @Param("flowtype") int flowtype,@Param("payId") int payId,
	                                                       @Param("startDate") Date startDate, @Param("endDate") Date endDate);

	@Select(" SELECT realname,username,integrated_tax_fee,project_amount " +
			" FROM zdd_crowdsource_project_item cpi " +
			" WHERE cpi.projectid = #{bizid} and cpi.deleted=0 ")
	List<CrowdsourcingLedgerDto> getCrowdsourcingLedger(@Param("bizid") int bizid);

	@Select(" SELECT cp.`realname`,cp.`username`,cp.project_pay_amount,cp.projectdealamount " +
			" FROM zdd_company_project cp  " +
			" WHERE cp.projectid = #{bizid}")
	CompanyProject getMakerLedger(@Param("bizid") int bizid);

	@Select(" select epi.entrustee_id,epi.entrustee_name as realname,epi.entrustee_studio as username," +
			" epi.amount as projectamount,epi.tax_inclusive_amount as integratedtaxfee" +
			" from zdd_entrustment_project_item epi " +
			" where epi.import_id = #{bizid}" +
			" and epi.id = #{itemId}" +
			" limit 1 ")
	CrowdsourcingLedgerDto getGroupMakerByBizId(@Param("bizid") int bizid,@Param("itemId") int itemId);

	@Select(" select epi.entrustee_id,epi.entrustee_name as realname,epi.entrustee_studio as username," +
			" sum(epi.amount) as projectamount,sum(epi.tax_inclusive_amount) as integratedtaxfee" +
			" from zdd_entrustment_project_item epi " +
			" where epi.import_id = #{bizid} " +
			" and epi.id = #{itemId} ")
	CrowdsourcingLedgerDto getEntrustmentByBizId(@Param("bizid") int bizid,@Param("itemId") int itemId);

	@Select(" select SUM(caf.flow_amount) AS flowAmount " +
			" from zdd_company_accountflow caf " +
			" join zdd_company c on c.compid = caf.compid " +
			" left join zdd_company_recharge cr on cr.rechargeid = caf.bizid and caf.biz_type = 4 " +
			" where caf.compid = #{compid} " +
			" and if(#{flowtype}=0,1=1,flowtype=#{flowtype}) " +
			" and caf.createtime between #{startDate} and #{endDate} " +
			" and caf.account_type in (${accountType}) " +
			" and concat(c.compname,IFNULL(cr.rechargeno,'')) like '%${name}%' " +
			" and caf.account_type in (${accountType}) " )
	CompanyAccountFlowDto businessFlowStatistics(@Param("compid") int compid, @Param("name") String name,
													   @Param("flowtype") int flowtype, @Param("accountType") String accountType,
													   @Param("startDate") Date startDate, @Param("endDate") Date endDate);

	@Select(" select SUM(caf.flow_amount) as flowAmount " +
			" from zdd_company_accountflow caf " +
			" join zdd_company c on c.compid = caf.compid " +
			" left join zdd_company_recharge cr on cr.rechargeid = caf.bizid and caf.biz_type = 4 " +
			" where caf.compid = #{compid} " +
			" and if(#{flowtype}=0,1=1,flowtype=#{flowtype}) " +
			" and caf.createtime between #{startDate} and #{endDate} " +
			" and caf.account_type in (${accountType}) " +
			" and concat(c.compname,IFNULL(cr.rechargeno,'')) like '%${name}%' " +
			" and caf.account_type in (${accountType}) ")
	CompanyAccountFlowDto enterpriseMakerGroupFlowStatistics(@Param("compid") int compid, @Param("name") String name,
														           @Param("flowtype") int flowtype, @Param("accountType") String accountType,
														           @Param("startDate") Date startDate, @Param("endDate") Date endDate);

}
